#-*- coding=utf-8 -*-
import web

#import from sysbuild
import time

#import from custom module
import utils

#renders
render = web.template.render('templates/',cache=False)

#databases
db = web.database(dbn='mysql',user='root',pw='root',db='blog')

urls = (
    '/','index',
    '/test','test',
    '/post/(.*)','viewPost',
    '/admin/post','post',
    '/admin/post/edit/(.*)','edit',
    '/admin/post/delete','delete',
    '/admin/tag/add','addTag',
    '/admin/tag/edit','editTag',
    '/admin/tag/delete','deleteTag',
    '/tag/getTags','getTags',
    '/tag/(.*)','viewbytag',
    '/login','login',
    '/logout','logout',
    '/comment/post','postComment',
    '/admin/comment/delete','deleteComment',
    )

class login:
    def GET(self):
        options_list = list(db.select('options'))
        options = {}
        for option_list in options_list:
            options[option_list.option_name] = option_list.option_value
        return render.login(options)
    
    def POST(self):
        form = web.input()
        result = db.query( "select * from users where user_login = $log and user_pass = $pwd", vars=form)
        if len(result)>0:
            session.user_login = form.log
        web.redirect('/')
    
class logout:
    def GET(self):
        session.kill()
        return web.redirect('/')

class index:
    def GET(self):
        options_list = list(db.select('options'))
        options = {}
        for option_list in options_list:
            options[option_list.option_name] = option_list.option_value
        posts = list(db.select('posts',order='post_date desc'))
        #根据提取出的posts取获得它们相应的tags,并且取得每个post的comment数量(放在此处做,也可以用其它方法做)
        tags = {}
        for post in posts:
            tag = []
            ts = db.select(['tags','tag_relationships'],what='tags.name',where='tags.tag_id=tag_relationships.tag_id and tag_relationships.object_id='+web.sqlquote(post.id))
            ts = list(ts)
            for t in ts:
                tag.append(t.name)
            tags[post.id] = tag
            #取出每个post的comment数量
            commentCount = db.query('select count(*) as count from comments where comment_post_id='+web.sqlquote(post.id))[0].count
            post['commentCount'] = commentCount
        return render.index(options,posts,tags,session)

class viewbytag:
    def GET(self,tag):
        tagids = db.select('tags',what='tag_id',where='name=$tagname',vars={'tagname':tag})
        if len(tagids)>0:
            tagid = tagids[0].tag_id
            #下面和index()中提取posts和options类似
            #select p.* from posts p,tag_relationships t where p.id=t.object_id and t.tag_id=16;
            options_list = list(db.select('options'))
            options = {}
            for option_list in options_list:
                options[option_list.option_name] = option_list.option_value
            posts = list(db.select(['posts','tag_relationships'],what='posts.*',where='posts.id=tag_relationships.object_id and tag_relationships.tag_id='+web.sqlquote(tagid)))
            #根据提取出的posts取获得它们相应的tags
            tags = {}
            for post in posts:
                tag = []
                ts = db.select(['tags','tag_relationships'],what='tags.name',where='tags.tag_id=tag_relationships.tag_id and tag_relationships.object_id='+web.sqlquote(post.id))
                ts = list(ts)
                for t in ts:
                    tag.append(t.name)
                tags[post.id] = tag
                #取出每个post的comment数量
                commentCount = db.query('select count(*) as count from comments where comment_post_id='+web.sqlquote(post.id))[0].count
                post['commentCount'] = commentCount
            return render.index(options,posts,tags,session)
        else:
            return 'sorry,this tag is not exist!'
    
class test:
    def GET(self):
        return render.test()

class viewPost:
    def GET(self,postid):
        #下面和index()中提取posts和options类似
        #select p.* from posts p,tag_relationships t where p.id=t.object_id and t.tag_id=16;
        options_list = list(db.select('options'))
        options = {}
        for option_list in options_list:
            options[option_list.option_name] = option_list.option_value
        posts = list(db.select('posts',where='posts.id='+web.sqlquote(postid)))
        if len(posts)<=0:
            return 'sorry,the post is not exist!'
        post = posts[0]
        #根据提取出的posts取获得它们相应的tags
        tags = []
        ts = list(db.select(['tags','tag_relationships'],what='tags.name',where='tags.tag_id=tag_relationships.tag_id and tag_relationships.object_id='+web.sqlquote(post.id)))
        for t in ts:
            tags.append(t.name)
        #根据postid提取它前一篇文章和后一篇文章,这里不知道其它系统是怎么实现的,我这里用post_date去判断,
        #并且是简化的判断处理
        #select * from posts where post_date<(select post_date from posts where id=61) order by post_date desc limit 1;
        #select * from posts where post_date>(select post_date from posts where id=60) order by post_date asc limit 1;
        nextPosts = list(db.query('select id from posts where post_date<(select post_date from posts where id='+web.sqlquote(postid)+') order by post_date desc limit 1'))
        if len(nextPosts)>0:
            post['next'] = nextPosts[0].id
        else:
            post['next'] = ''
        previousPosts = list(db.query('select id from posts where post_date>(select post_date from posts where id='+web.sqlquote(postid)+') order by post_date asc limit 1'))
        if len(previousPosts)>0:
            post['previous'] = previousPosts[0].id
        else:
            post['previous'] = ''
        #取出该post的comments
        comments = list(db.select('comments',where='comment_post_id='+web.sqlquote(postid)))
        post['comments'] = comments
        return render.viewpost(options,post,tags,session)
    
class addTag():
    def GET(self):
        pass
    
    def POST(self):
        pass
    
class editTag():
    def GET(self):
        pass
    
    def POST(self):
        pass
    
class deleteTag():
    def GET(self):
        pass
    
    def POST(self):
        pass
    
class getTags():
    def GET(self):
        web.header('Content-Type','text/xml')
        web.header('charset','UTF-8')
        result = r'<?xml version = "1.0" ?><tags>'
        tags = list(db.select('tags',what='name'));
        temp = ''
        for tag in tags:
            temp += '<tag>'+tag.name+'</tag>'
        result = result + temp + '</tags>'
        return result

class post:
    """081204:0030-0145
    tags处理方式:
    从post_tags得到如:java,python,c,java这样的一个字符串后,
    1.先用,号分割,然后去除重复元素;
    2.查找tags表中是否有此tag了,如果有则直接
    取出对应的tag_id,如果没有则建立并取出建立后的tag_id,
    同时初始化插入tag_taxonomy中一条记录,count初始化为0;
    3.开始数据库事务操作处理;
    4.往posts表插入磁此篇日志,并得到它的id;
    5.建立tag_relationships中的post<->tag对应关系,多个tag
    则建立多条记录,且在tag_taxonomy中更每个tag的count+1;
    6.数据库事务操作结束,没有异常则提交commit;
    """
    def GET(self):
        if not session.user_login:
            web.redirect('/login')
        return render.post({})
    
    def POST(self):
        if not session.user_login:
            web.redirect('/login')
        form = web.input()
        tags = form.post_tags;
        #start 1
        tags = tags.split(',')
        tags = utils.getUniqueValuesForList(tags)
        #end 1
        #start 2
        tags_id = []
        for tag in tags:
            t = db.query('select tag_id as tag_id from tags where name=$tag',vars={'tag':tag})
            if len(t)>0:
                tags_id.append(t[0].tag_id)
            else:
                temp_id = db.insert('tags',name=tag)
                tags_id.append(temp_id)
                db.insert('tag_taxonomy',tag_id=temp_id,count=0)
        #end 2
        #start 3
        transaction = db.transaction()
        try:
            #start 4
            #把content中的id="code"替换成name="code",tinymce过滤掉了name元素,这里替换回来
            form.post_content = form.post_content.replace('id=\"code\"','name=\"code\"')
            post_id = db.insert('posts',post_title=form.post_title,post_content=form.post_content,post_date=web.SQLLiteral("now()"))
            #end 4
            #start 5
            for tagid in tags_id:
                db.insert('tag_relationships',object_id=post_id,tag_id=tagid)
                """
                count_temp = db.query('select count as c from tag_taxonomy where tag_id=$tag',vars={'tag':tagid})[0].c
                count_temp = count_temp+1
                db.update('tag_taxonomy','tag_id='+web.sqlquote(tagid),count=count_temp)
                """
                #Tzury Bar Yochay told me do it like this:
                db.query('update tag_taxonomy set count=count+1 where tag_id='+web.sqlquote(tagid))
            #end 5
        except:
            transaction.rollback()
            raise
        else:
            transaction.commit()
        #end 3=6
        web.redirect('/')
        
class edit:
    def GET(self, post_id):
        if not session.user_login:
            web.redirect('/login')
        try:
            post = db.select('posts', where ='id = '+post_id)[0]
            #根据提取出的posts取获得它们相应的tags
            tag = ''
            ts = db.select(['tags','tag_relationships'],what='tags.name',where='tags.tag_id=tag_relationships.tag_id and tag_relationships.object_id='+web.sqlquote(post.id))
            ts = list(ts)
            for t in ts:
                tag = tag+t.name+','
            post['tags'] = tag
            return render.post(post)
        except:
            return 'This post doesn\'t exist!'

    def POST(self,post_id):
        if not session.user_login:
            web.redirect('/login')
        form = web.input()
        #originaltags是修改之前的tags,post_tags是修改之后的tags
        originaltags = utils.getUniqueValuesForList(form.originaltags.split(','))
        aftertags = utils.getUniqueValuesForList(form.post_tags.split(','))
        #如果tags元素没有改变(值没有改变),就不需要进行tags的操作了
        if utils.isListAEqualsListB(originaltags,aftertags):
            #把content中的id="code"替换成name="code",tinymce过滤掉了name元素,这里替换回来
            form.post_content = form.post_content.replace('id=\"code\"','name=\"code\"')
            n = db.update('posts', 'id='+form.post_id, post_title=form.post_title, post_content=form.post_content,_test=False)
        else:
            #不相同则进行数据库事务操作,更新post的同时还需进行tags等等的更新
            #如果tags进行了修改,一个则是增加的,一个则是减少的,增加的按照post()
            #中的处理,减少的按照delete()中的处理
            #新增加的tag
            newtags = []
            #删除了的tag
            deletedtags = []
            for originaltag in originaltags:
                if originaltag in aftertags:
                    aftertags.remove(originaltag)
                else:
                    #如果原tag在修改后的tags中没有则代表此tag已经删除了
                    deletedtags.append(originaltag)
            #最后剩下的就是新增加的tags
            newtags = aftertags
            #进行数据库事务处理
            transaction = db.transaction()
            try:
                #处理新增的tags,如果tags表中没有则新建立
                tags_id = []
                for tag in newtags:
                    t = db.query('select tag_id as tag_id from tags where name=$tag',vars={'tag':tag})
                    if len(t)>0:
                        tags_id.append(t[0].tag_id)
                    else:
                        temp_id = db.insert('tags',name=tag)
                        tags_id.append(temp_id)
                        db.insert('tag_taxonomy',tag_id=temp_id,count=0)
                #post<->tag关系,tag count更新
                for tagid in tags_id:
                    db.insert('tag_relationships',object_id=form.post_id,tag_id=tagid)
                    db.query('update tag_taxonomy set count=count+1 where tag_id='+web.sqlquote(tagid))
                
                #处理删除的tags,获得它们的tag_id
                tags_id = []
                for tag in deletedtags:
                    t = db.query('select tag_id as tag_id from tags where name=$tag',vars={'tag':tag})
                    if len(t)>0:
                        tags_id.append(t[0].tag_id)
                #删除post<->tag关系,tag count更新,可以用下面拼凑sql语句执行的方式,但此处暂不用:
                #delete from tag_relationships where (object_id=50 and tag_id=17) or (object_id=50 and tag_id=18);
                #update tag_taxonomy set count=count-1 where (object_id=50 and tag_id=17) or (object_id=50 and tag_id=18);
                for tagid in tags_id:
                    db.query('delete from tag_relationships where object_id='+web.sqlquote(form.post_id)+' and tag_id='+web.sqlquote(tagid))
                    db.query('update tag_taxonomy set count=count-1 where tag_id='+web.sqlquote(tagid))
                
                #post的更新
                #把content中的id="code"替换成name="code",tinymce过滤掉了name元素,这里替换回来
                form.post_content = form.post_content.replace('id=\"code\"','name=\"code\"')
                n = db.update('posts', 'id='+form.post_id, post_title=form.post_title, post_content=form.post_content,_test=False)
            except:
                transaction.rollback()
                raise
            else:
                transaction.commit()
        web.redirect('/')

class delete:
    """
    删除文章,必须同时删除tag_relationships和tag_taxonomy中值
    """
    def POST(self):
        if not session.user_login:
            web.redirect('/login')
        form = web.input()
        #先取出post所有对应的tag_id
        tag_ids = db.select('tag_relationships',what='tag_id',where='object_id='+web.sqlquote(form.post_id))
        transaction = db.transaction()
        try:
            for tagid in tag_ids:
                #!此时的tagid还是dict类型,得到tag_id还需要用tagid.tag_id
                db.query('update tag_taxonomy set count=count-1 where tag_id='+web.sqlquote(tagid.tag_id))
            db.delete('tag_relationships','object_id='+web.sqlquote(form.post_id))
            db.delete('posts','id='+web.sqlquote(form.post_id))
        except:
            transaction.rollback()
            raise
        else:
            transaction.commit()
        web.redirect('/')
    
class postComment:
    def GET(self):
        pass
    
    def POST(self):
        form = web.input()
        commentid = db.insert('comments',comment_post_id=form.postid,comment_author=form.author,comment_author_email=\
                  form.email,comment_author_url=form.url,comment_date=web.SQLLiteral("now()"),comment_content=\
                  form.content,comment_author_ip=web.ctx.ip)
        web.redirect('/post/'+str(form.postid)+'#comment-'+str(commentid))
        
class deleteComment:
    def POST(self):
        if not session.user_login:
            web.redirect('/login')
        form = web.input()
        db.delete('comments','comment_id='+web.sqlquote(form.commentid))
        web.redirect('/post/'+str(form.postid)+'#comments')
        
app = web.application(urls,globals(),autoreload=True)

if web.config.get('_session') is None:
    store = web.session.DBStore(db, 'sessions')
    #session = web.session.Session(app, web.session.DiskStore('sessions'), {'count': 0})
    session = web.session.Session(app, store, {'user_login':''})
    web.config._session = session
else:
    session = web.config._session

if __name__ == '__main__':
    #to solve the slow speed problem which caused by 
    #serving text files in the static fold
    import httppatch
    app.run()
    